﻿using CCWin;
using DBToExcel.Code;
using DBToExcel.Model;
using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Linq;
using System.Data;

namespace DBToExcel
{
    public partial class EditTempLate : Skin_Metro
    {
        private string _id = string.Empty;
        public event Action<bool> completeEvent = null;
        public EditTempLate(string id)
        {
            InitializeComponent();
            this._id = id;
            LoadConns();
            this.startrowindex.SkinTxt.TextChanged += SkinTxt_TextChanged;
            this.exceltemplate.SkinTxt.Click += exceltemplate_Click;
        }

        private void SkinTxt_TextChanged(object sender, EventArgs e)
        {
            string text = this.startrowindex.Text;
            int value = 0;
            if (!int.TryParse(text, out value))
            {
                this.startrowindex.Text = "0";
            }
            else
            {
                this.startrowindex.Text = value.ToString();

            }
        }

        private void LoadConns()
        {
            List<MDBConn> list = ExcelBuilderService.QueryAllMDBConns();
            if (list == null || list.Count == 0)
            {
                return;
            }

            list.ForEach(x =>
            {
                this.dbconn.Items.Add(new ComboBoxItem() { Text = x.Name, Value = x.Id });
            });
        }

        private void istemplate_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch (this.istemplate.SelectedIndex)
            {
                case 0:
                    this.exceltemplate.Visible = false;
                    this.startrowindex.Visible = false;
                    this.exceltemplatelab.Visible = false;
                    this.startrowlab.Visible = false;
                    break;
                case 1:
                    this.exceltemplate.Visible = true;
                    this.startrowindex.Visible = true;
                    this.exceltemplatelab.Visible = true;
                    this.startrowlab.Visible = true;
                    break;
                default:
                    break;
            }
        }

        private void exceltemplate_Click(object sender, EventArgs e)
        {
            //Excel文件|*.xls|
            this.openFileDialog1.Filter = "Excel文件|*.xlsx";
            DialogResult result = this.openFileDialog1.ShowDialog(this);
            if (result == DialogResult.OK)
            {
                this.exceltemplate.Text = this.openFileDialog1.SafeFileName;
                this.exceltemplate.Tag = this.openFileDialog1.FileName;
            }
        }

        private void querybtn_Click(object sender, EventArgs e)
        {
            ComboBoxItem item = this.dbconn.SelectedItem as ComboBoxItem;
            if (item == null)
            {
                MessageBoxEx.Show("请选择数据连接");
                return;
            }

            MDBConn conn = ExcelBuilderService.QueryAllMDBConns().FirstOrDefault(x => x.Id.Equals(item.Value));
            if (conn == null)
            {
                MessageBoxEx.Show("不存在此选中的连接信息");
                return;
            }

            string sql = this.datasql.Text;
            if (string.IsNullOrEmpty(sql))
            {
                MessageBoxEx.Show("请输入数据SQL");
                return;
            }

            Loading.ShowLayer(this, () =>
            {
                try
                {
                    IDbConnection connection = DBConnUtils.GetConn(conn.FileName, conn.ClassName, conn.ConnStr);
                    DataTable table = ExcelBuilderService.QueryDataTable(connection, sql);
                    List<string> cols = table.Columns.Cast<DataColumn>().Select(x => x.Caption).ToList();
                    DataGridViewRowCollection rows = this.DataG_SqlMap.Rows;
                    rows.Clear();
                    DataRowCollection dataRows = table.Rows;
                    //if (dataRows == null || dataRows.Count == 0)
                    //{
                    //    return;
                    //}

                    List<MExcelMap> maps = null;
                    if (!string.IsNullOrEmpty(this._id))
                    {
                        MTempAllInfo allInfo = ExcelBuilderService.QueryAllInfo(this._id);
                        if (allInfo != null)
                        {
                            maps = allInfo.Map;
                        }
                    }

                    DataRow first = dataRows!=null&& dataRows.Count>0? dataRows[0]:null;
                    int rowCount = rows.Add(cols.Count);

                    for (int i = 0; i <= rowCount; i++)
                    {
                        rows[i].Cells["FieldName"].Value = cols[i];
                        if (maps != null && maps.Count > 0)
                        {
                            MExcelMap temp = maps.FirstOrDefault(x => x.FieldName.Equals(cols[i]));
                            if (temp != null)
                            {
                                rows[i].Cells["excelIndex"].Value = temp.ExcelCellIndex;
                                rows[i].Cells["script"].Value = temp.Script;
                            }
                        }

                        if (first==null)
                        {
                            continue;
                        }

                        if (first[cols[i]] == null)
                        {
                            continue;
                        }

                        if (first[cols[i]] is DateTime)
                        {
                            rows[i].Cells["tempvalue"].Value = ((DateTime)first[cols[i]]).ToString("yyyy-MM-dd HH:mm:ss.fff");
                        }
                        else
                        {
                            rows[i].Cells["tempvalue"].Value = first[cols[i]].ToString();
                        }

                    }
                }
                catch (Exception ex)
                {
                    MessageBoxEx.Show(ex.Message);
                }
            });
        }

        private void DataG_SqlMap_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex != 4 || e.RowIndex == -1)
            {
                return;
            }
            object value = this.DataG_SqlMap.Rows[e.RowIndex].Cells[e.ColumnIndex].Value;
            ScriptForm scriptForm = new ScriptForm(e, value == null ? string.Empty : value.ToString());
            scriptForm.SaveScriptEvent += ScriptForm_SaveScriptEvent;
            scriptForm.ShowDialog(this);
        }

        private bool ScriptForm_SaveScriptEvent(object arg1, string arg2)
        {
            DataGridViewCellEventArgs e = (DataGridViewCellEventArgs)arg1;
            this.DataG_SqlMap.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = arg2;
            return true;
        }

        private void savebtn_Click(object sender, EventArgs e)
        {
            MTempAllInfo allInfo = new MTempAllInfo();
            allInfo.Template = new MTemplate();
            allInfo.Template.Id = this._id;
            allInfo.Template.Name = this.tempname.Text;
            if (string.IsNullOrEmpty(allInfo.Template.Name))
            {
                MessageBoxEx.Show("请输入模板名称");
                return;
            }

            ComboBoxItem comboBoxItem = this.dbconn.SelectedItem as ComboBoxItem;
            if (comboBoxItem == null)
            {
                MessageBoxEx.Show("请选择数据连接");
                return;

            }

            allInfo.Template.ConnId = comboBoxItem.Value.ToString();

            allInfo.Template.IsTempLate = this.istemplate.SelectedIndex;
            if (allInfo.Template.IsTempLate == -1)
            {
                MessageBoxEx.Show("请选择是否模板生成");
                return;
            }

            allInfo.Template.OrgFileName = this.exceltemplate.Text;
            allInfo.Template.TempPlateExcelFile = this.exceltemplate.Tag.ToString();
            allInfo.Template.StartRowIndex = Convert.ToInt32(this.startrowindex.Text);

            if (allInfo.Template.IsTempLate == 1)
            {
                if (string.IsNullOrEmpty(allInfo.Template.OrgFileName))
                {
                    MessageBoxEx.Show("请选择Excel模板");
                    return;
                }
            }
      

            allInfo.Template.DataSql = this.datasql.Text;
            if (string.IsNullOrEmpty(allInfo.Template.DataSql))
            {
                MessageBoxEx.Show("请输入数据SQL");
                return;
            }

            List<MExcelMap> maps = new List<MExcelMap>();
            DataGridViewRowCollection rows = this.DataG_SqlMap.Rows;
            if (rows == null || rows.Count == 0)
            {
                MessageBoxEx.Show("字段映射不能为空");
                return;
            }

            foreach (DataGridViewRow item in rows)
            {
                maps.Add(new MExcelMap()
                {
                    TemplateId = this._id,
                    ExcelCellIndex = item.Cells["excelIndex"].Value != null ? item.Cells["excelIndex"].Value.ToString() : string.Empty,
                    FieldName = item.Cells["FieldName"].Value != null ? item.Cells["FieldName"].Value.ToString() : string.Empty,
                    Script = item.Cells["script"].Value != null ? item.Cells["script"].Value.ToString() : string.Empty,
                    TempValue= item.Cells["tempvalue"].Value != null ? item.Cells["tempvalue"].Value.ToString() : string.Empty,
                }); 
            }

            allInfo.Map = maps;
            this._id = ExcelBuilderService.Save(allInfo);
            this.exceltemplate.Tag = allInfo.Template.TempPlateExcelFile;
            this.exceltemplate.Text= allInfo.Template.OrgFileName;
            this.startrowindex.Text = allInfo.Template.StartRowIndex.ToString();
            MessageBoxEx.Show("保存成功");
        }

        private void LoadTempLateData()
        {
            MTempAllInfo tempAllInfo = ExcelBuilderService.QueryAllInfo(this._id);
            if (tempAllInfo == null)
            {
                this._id = null;
                return;
            }

            this.tempname.Text = tempAllInfo.Template.Name;
            ComboBoxItem conn = this.dbconn.Items.Cast<ComboBoxItem>().FirstOrDefault(x => x.Value.Equals(tempAllInfo.Template.ConnId));
            this.dbconn.SelectedItem = conn;
            this.istemplate.SelectedIndex = tempAllInfo.Template.IsTempLate;
            this.exceltemplate.Text = tempAllInfo.Template.OrgFileName;
            this.exceltemplate.Tag = tempAllInfo.Template.TempPlateExcelFile;
            this.startrowindex.Text = tempAllInfo.Template.StartRowIndex.ToString();
            this.datasql.Text = tempAllInfo.Template.DataSql;

            List<MExcelMap> maps = tempAllInfo.Map;
            int lastIndex = this.DataG_SqlMap.Rows.Add(maps.Count);
            DataGridViewRowCollection rows = this.DataG_SqlMap.Rows;
            for (int i = 0; i <= lastIndex; i++)
            {
                rows[i].Cells["FieldName"].Value = maps[i].FieldName;
                rows[i].Cells["excelIndex"].Value = maps[i].ExcelCellIndex;
                rows[i].Cells["script"].Value = maps[i].Script;
                rows[i].Cells["tempvalue"].Value = maps[i].TempValue;
            }
        }

        private void EditTempLate_Load(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(this._id))
            {
                LoadTempLateData();
            }
        }

        private void autofillsn_Click(object sender, EventArgs e)
        {
            DataGridViewRowCollection rows=   this.DataG_SqlMap.Rows;
            if (rows==null||rows.Count==0)
            {
                return;
            }

            bool sure = true;
            if (rows.Cast<DataGridViewRow>().Count(x=>x.Cells["excelIndex"].Value!=null)>0)
            {
                if (DialogResult.OK!=MessageBoxEx.Show("列索引已经有填充值，自动填充会覆盖原值，确认填充？"))
                {
                    sure = false;
                }
            }

            if (!sure)
            {
                return;
            }

            for (int i = 0; i < rows.Count; i++)
            {
                rows[i].Cells["excelIndex"].Value = i;
            }
        }
    }
}
